Odpověď: Re: backend don't use index whenquerying by indexed column

Dear Tom,
first of all thank You for Your response. But here is the
reality. Table formula have about 450000 rows at this time.
The id_formula column is int4 defined as not null primary key. And the index
have clause unique, but backend still don't use it. I have another column in
this table named id_loan. It's int4 too, but it's not unique, but there are 18
to 50 rows with the same value in this column. I have tried to create index
using btree or hash on this column, the index was created, but backend don't
use it. Even i add id_loan = id_loan to SELECT, then backend use the index. I
can't understand to that. If you will need other informations, please ask me.

Thank You very much
Zdenek Habala
>
> Od : Tom Lane <tgl@sss.pgh.pa.us>
> Datum : 2001/08/07 Út dop. 01:55:56 GMT+02:00
> Komu : zhabala@telecom.cz,  pgsql-bugs@postgresql.org
> Předmět : Re: [BUGS] backend don't use index when querying by indexed column
>
> pgsql-bugs@postgresql.org writes:
> > backend don't use index when querying by indexed column
>
> This is not necessarily a bug.
>
> > when i try to explain this select i will got this:
>
> > explain select * from formula where id_formula = 1;
> > NOTICE:  QUERY PLAN:
> > Seq Scan on formula  (cost=0.00..10919.89 rows=4576 width=72)
>
> How many rows altogether in this table?  How many actually have
> id_formula = 1?   It would appear from the rows=4576 estimate that there's
> at least one value in the table that occurs thousands of times.
>
> > but when i try to explain modified select like this:
> > explain select * from formula where id_formula = 1 and
id_formula=id_formula;
> > NOTICE:  QUERY PLAN:
> >  Index Scan using predpis_id_formula_key on formula  (cost=0.00..11952.57
rows=46
> >  width=72)
>
> > backend uses the index.
>
> I think the extra time per-row to evaluate the extra WHERE clause pushes
> up the estimated cost of the seq scan just enough to make it a little
> more expensive than the estimated cost of the indexscan (note that
> they're pretty close in the two examples).  The seqscan will have to
> evaluate two operators for every row, whereas the indexscan only has to
> do it at the rows found by the index, so its cost goes up less when you
> add more WHERE conditions.
>
> If these estimates have nothing to do with reality in your situation,
> then that's a bug.  But you haven't told us anything about what reality
> is.  If the planner's estimates are correct then it's doing the right
> thing.
>
>             regards, tom lane
>